﻿Namespace Muhasebe.Yaslilik
	Module Functions

		Function HesapBolgeDagilim(ByVal Process As Token, ByVal yil As Integer, ByVal ay As Integer) As Recordset
			Q("select ym.hturu, substring(h.hesapno,1,6) as usthesap, h.hesapno, ym.bolgekod, count(*) as adet,")
			Q("sum(ym.maas) as maas, sum(ym.fon) as fon, sum(ym.devlet) as devlet, sum(ym.tmtfark) as tmt")
			Q("from yaslılık..yasmaas ym left join yaslılık..yashesaptur h on ym.hturu = h.hturu")
			Q("where ym.yil = @yil and ym.ay = @ay")
			Q("group by ym.hturu, substring(h.hesapno,1,6), h.hesapno, ym.bolgekod")
			Q("order by substring(h.hesapno,1,6), h.hesapno, ym.bolgekod")
			Return Process.OpenSQL("*", yil, ay)
		End Function

		Function Dagilim(ByVal Process As Token, donem As String) As Recordset
			Q("select t.hesapno,b.hesapadi,sum(tutar) as tutar from (")
			Q("select b.fonhesap as hesapno,a.fon as tutar from yaslılık..yasmaas a inner join yaslılık..yashesaptur b on a.hturu = b.hturu where yil = @yil and ay = @ay union all")
			Q("select b.devhesap as hesapno,a.devlet as tutar from yaslılık..yasmaas a inner join yaslılık..yashesaptur b on a.hturu = b.hturu where yil = @yil and ay = @ay union all")
			Q("select b.tmthesap as hesapno,a.tmtfark as tutar from yaslılık..yasmaas a inner join yaslılık..yashesaptur b on a.hturu = b.hturu where yil = @yil and ay = @ay) t")
			Q("left join muhasebe..hesplan b on t.hesapno = b.hesapno")
			Q("where not t.hesapno is null")
			Q("group by t.hesapno,b.hesapadi order by t.hesapno")
			Return Process.OpenSQL("*", Mid(donem, 1, 4), Mid(donem, 5, 4))
		End Function

		Function OdemeEmriYaz(ByVal Process As Token, donem As String, tarih As Date, acik As String, Rs As Recordset, kaykul As String) As Result
			Dim eno As Integer
			Try
				Process.BeginTran()
				eno = Process.OpenSQL("select fisno  from muhasebe..fisno updlock")(0)
				Process.ExecSQL("update muhasebe..fisno set fisno = fisno + 1")
				Dim islemtar = Process.SysTime
				Q("select min(cekno) as cekbasno,max(cekno) as cekbitno,count(*) as adet,sum(miktar) as tutar")
				Q("from muhasebe..cek where yil = @yil and ay = @ay and cekkod = 'C'")
				Dim Rst = Process.OpenSQL("*", Mid(donem, 1, 4), Mid(donem, 5, 4))
				Q("update muhasebe..cek set odeemrino = @eno, onay = 1")
				Q("where yil = @yil and ay = @ay and cekkod = 'C'")
				Process.ExecSQL("*", eno, Mid(donem, 1, 4), Mid(donem, 5, 4))
				Dim Rsg = Process.OpenSQL("select * from muhasebe.dbo.odemeemri where eno = @eno", eno)
				Rsg.AddNew()
				Rsg.SetValues("eno,tarih,alacakli", eno, tarih, acik)
				Rsg.SetValues("cekkod,cekbasno,cekbitno", "C", Rst("cekbasno"), Rst("cekbitno"))
				Rsg.SetValues("ceksay,cektoplam,odemik", Rst("adet"), Rst("tutar"), Rst("tutar"))
				Rsg.SetValues("acik,islemtar,kaykul,onay", acik, islemtar, kaykul, 0)
				Rsg.Save()
				Dim Rsd = Process.OpenSQL("select * from muhasebe.dbo.odemeemridetay where eno = @eno", eno)
				For Each Row In Rs
					Dim h As String = Rs("hesapno")
					Rsd.AddNew()
					Rsd.SetValues("eno,tarih,hesapno,talihesap,anahesap,gruphesap", eno, tarih, h, h.Mid(1, 5), h.Mid(1, 3), h.Mid(1, 1))
					Rsd.SetValues("acik,borc,alacak,bakiye,kaykul,islemtar", acik, Rs("tutar"), 0, -Rs("tutar"), kaykul, islemtar)
				Next
				Rsd.AddNew()
				Rsd.SetValues("eno,tarih,hesapno,talihesap,anahesap,gruphesap", eno, tarih, "1060127", "10601", "106", "1")
				Rsd.SetValues("acik,borc,alacak,kaykul,islemtar", acik, 0, Rs.Sum("tutar"), kaykul, islemtar)
				Rsd.Save()
				Process.CommitTran()
			Catch ex As Exception
				Return Result.Error(ex.Message)
			End Try
			Return Result.OK(eno)
		End Function

		Function CekListesi(ByVal Process As Token, donem As String) As Recordset
			Q("select * from (")
			Q("select a.cekno as refno,a.adi+' '+a.soyadi as alacakli,a.kimlik,a.maas as tutar,1 as yazdir from yaslılık..yasmaas a where cekno is not null and yil = @yil and ay = @ay union all")
			Q("select a.cekno,b.acik,'',a.miktar,0 from yaslılık..yasbankacek a left join yaslılık..odemeyeri b on a.odeyerkod = b.odeyerkod where yil = @yil and ay = @ay) t")
			Q("order by refno")
			Return Process.OpenSQL("*", Mid(donem, 1, 4), Mid(donem, 5, 4))
		End Function

		Function CekAktar(ByVal Process As Token, donem As String, tarih As Date, kaykul As String) As Result
			Try
				Process.BeginTran()
				Dim islemtar = Process.SysDate
				Dim Rsd = Process.OpenSQL("select * from muhasebe.dbo.cek where yil = @yil and ay = @ay", Mid(donem, 1, 4), Mid(donem, 5, 4))
				Dim Rs = CekListesi(Process, donem)
				For Each Row In Rs
					Rsd.AddNew()
					Rsd.SetValues("cekkod,cekno,kime,kimlik", "C", Rs("refno"), Rs("alacakli"), Rs("kimlik"))
					Rsd.SetValues("miktar,kaykul,islemtar,tarih,ay,ayeki,yil,yazdir", Rs("tutar"), kaykul, islemtar, tarih, Mid(donem, 5, 4), 0, Mid(donem, 1, 4), Rs("yazdir"))
				Next
				Rsd.Save()
				Process.CommitTran()
			Catch ex As Exception
				Return Result.Error(ex.Message)
			End Try
			Return Result.OK("")
		End Function

	End Module

End Namespace
